package com.ht.api.util;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;

import com.ht.api.db.Db;
import com.ht.api.db.Entity;
import com.ht.api.db.util.RedisUtil;

import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import lombok.extern.slf4j.Slf4j;

@Slf4j
public class RuleSqlUtil {

    /**
     * 创建条件sql
     * @param condition
     * @param params
     * @return
     */
    public static String buildConditionSql (JSONObject condition, List<Object> params) {
        if (condition == null) {
            return null;
        }

        AtomicInteger aliasCounter = new AtomicInteger(0); // 计数器，用于表的别名使用

        JSONObject rule = condition.getJSONObject("rule");
        JSONObject tag = condition.getJSONObject("tag");

        String ruleSql = buildRuleSql(rule, params, aliasCounter);
        String tagSql = buildTagSql(tag);

        // 根据获取到的规则条件sql和标签条件sql的结果进行合并
        StringBuilder finalSql = StrUtil.builder();
        if (ruleSql != null && tagSql != null) {
            finalSql.append("select bitmap_and(rule.id, tag.id) id from ");
            finalSql.append("(").append(ruleSql).append(") rule, ");
            finalSql.append("(").append(tagSql).append(") tag ");
        } else if (ruleSql != null && tagSql == null) {
            finalSql.append(ruleSql);
        } else if (ruleSql == null && tagSql != null) {
            finalSql.append(tagSql);
        }

//        System.out.println("Generated SQL: \n\n " +  SqlFormatter.format(finalSql.toString()));
//        System.out.println("\nParameters: " + params);

        return finalSql.toString();
    }

    /**
     * 创建tag规则sql
     * @param tag
     * @return
     */
    private static String buildTagSql (JSONObject tag) {
        if (tag == null) {
            return null;
        }

        String tagCode = tag.getStr("tag_code");
        JSONArray include = tag.getJSONObject("tag_ids").getJSONArray("include");
        JSONArray exclude = tag.getJSONObject("tag_ids").getJSONArray("exclude");

        // 根据标签的规则组装sql
        StringBuilder sql = StrUtil.builder();
        if (include == null && exclude == null) {
            return null;
        } else if (include != null && exclude == null) {//只有包含没有排除
            sql.append("select bitmap_union(to_bitmap(relation_id)) id from tag_relation where status = 0 and tag_id in ("+include.join(",")+")  ");

        } else if (include == null && exclude != null) {//只有排除没有包含
            sql.append("select bitmap_union(to_bitmap(relation_id)) id from tag_relation ");
            sql.append("where tag_code = '"+tagCode+"' and relation_id not in (").append("select relation_id from tag_relation where status = 0 and tag_id in ("+exclude.join(",")+")");

        } else if (include != null && exclude != null) {//排除和包含都同时存在
            sql.append("select bitmap_not(include.id, exclude.id) id from ");
            sql.append("(select bitmap_union(to_bitmap(relation_id)) id from tag_relation where status = 0 and tag_id in ("+include.join(",")+") ) include, ");
            sql.append("(select bitmap_union(to_bitmap(relation_id)) id from tag_relation where status = 0 and tag_id in ("+exclude.join(",")+") ) exclude");
        }

        return sql.toString();
    }

    /**
     * 对规则进行分组处理
     * @param rules
     * @return
     */
    public static Map<String, JSONObject> buildGroup (JSONArray rules) {
        //主要目的是将同一节点下的同一张表的条件进行合并处理，为下面创建sql提供便利，避免同一级的同一表的不同条件分开查询

        Map<String, JSONObject> groupMap = new HashMap<>();

        for (int i = 0; i < rules.size(); i++) {
            JSONObject rule = rules.getJSONObject(i);

            if (rule.containsKey("combine")) {
                if (!groupMap.containsKey("subRules")) {
                    JSONObject ruleObj = JSONUtil.createObj();
                    ruleObj.set("rules", JSONUtil.createArray());
                    groupMap.put("subRules", ruleObj);
                }

                groupMap.get("subRules").getJSONArray("rules").add(rule);
            } else {
                String key = rule.getStr("key");

                Entity columnMapping = RedisUtil.get("rule_key" + key);
                Entity tableMapping = RedisUtil.get("rule_mapping" + columnMapping.get("mapping_id"));

                Entity columnEntity = RedisUtil.get("db_column" + columnMapping.getStr("column_id"));

                String table = tableMapping.getStr("table_code");
                String column = columnEntity.getStr("code");
                String columnType = columnEntity.getStr("type");

                if (!groupMap.containsKey(table)) {
                    JSONObject ruleObj = JSONUtil.createObj();
                    ruleObj.set("rules", JSONUtil.createArray());
                    ruleObj.set("select_id", tableMapping.getStr("select_id"));
                    groupMap.put(table, ruleObj);
                }

                rule.set("is_relation", columnMapping.get("is_relation") != null && columnMapping.getInt("is_relation") == 1 ? true : false);
                rule.set("relation_config", columnMapping.get("relation_config"));

                rule.set("column", column);
                rule.set("column_type", columnType);
                groupMap.get(table).getJSONArray("rules").add(rule);
            }
        }

        return groupMap;
    }

    /**
     * 创建规则sql
     * @param ruleObj
     * @param params
     * @param aliasCounter
     * @return
     */
    private static String buildRuleSql (JSONObject ruleObj, List<Object> params, AtomicInteger aliasCounter) {
        StringBuilder finalSql = new StringBuilder();

        if (ruleObj == null) {
            return null;
        }

        String combine = ruleObj.getStr("combine");
        
        List<StringBuilder> sqlList = new ArrayList<>();
        JSONArray rules = ruleObj.getJSONArray("rules");

        Map<String, JSONObject> groupMap = buildGroup(rules);
        for (String group : groupMap.keySet()) {

            JSONArray groupRules = groupMap.get(group).getJSONArray("rules");

            if (group.equals("subRules")) {// 当前节点如果还有下级的时候，进行递归处理
                for (Object combineRule : groupRules) {
                    StringBuilder sql = StrUtil.builder();
                    sql.append(buildRuleSql((JSONObject) combineRule, params, aliasCounter));

                    sqlList.add(sql);
                }
            } else {
                String table = group;
                StringBuilder sql = StrUtil.builder();

                // 拿到当前需要查询的字段对象配置
                JSONObject selectObj = groupMap.get(group).getJSONObject("select_id");
                Entity selctColumnEntity = RedisUtil.get("db_column" + selectObj.getStr("select_column"));
                String selectColumn = selctColumnEntity.getStr("code");

                if (selectObj.containsKey("relation_talbe")) {// 如果查询的字段为关联表，则需要做特殊处理
                    Entity relationSelectColumnEntity = RedisUtil.get("db_column" + selectObj.getStr("relation_select_column"));
                    String relationSelectColumn = relationSelectColumnEntity.getStr("code");

                    String relationTable = selectObj.getStr("relation_talbe");

                    sql.append("SELECT bitmap_union(to_bitmap("+relationSelectColumn+")) AS id FROM ").append(relationTable).append(" WHERE ");

                    sql.append("bitmap_contains((");

                    sql.append("SELECT bitmap_union(bitmap_hash("+selectColumn+")) AS id FROM ").append(table).append(" WHERE ");
                } else { // 未配置关联关系，直接查询
                    sql.append("SELECT bitmap_union(to_bitmap("+selectColumn+")) AS id FROM ").append(table).append(" WHERE ");
                }

                // 组装where条件
                for (int i = 0; i < groupRules.size(); i++) {
                    JSONObject rule = groupRules.getJSONObject(i);
                    appendCondition(sql, params, rule);

                    if (i < groupRules.size() - 1) {
                        sql.append(" " + combine + " ");
                    }
                }

                // 当有关联关系，则需要组装后续sql
                if (selectObj.containsKey("relation_talbe")) {
                    Entity relationWhereColumnEntity = RedisUtil.get("db_column" + selectObj.getStr("relation_where_column"));
                    String relationWhereColumn = relationWhereColumnEntity.getStr("code");

                    sql.append("), ").append("bitmap_to_string(bitmap_hash(" + relationWhereColumn+ ")))");
                }

                sqlList.add(sql);
            }

        }

        // 同一组下的子查询结果根据配置进行合并
        if (sqlList.size() > 1) {

            finalSql.append("SELECT ").append(combine.equals("AND") ? "BITMAP_AND" : "BITMAP_UNION");

            StringBuilder subQuery = StrUtil.builder();
            finalSql.append("(");
            for (int i = 0; i < sqlList.size(); i++) {
                String alias = "t" + aliasCounter.incrementAndGet();

                finalSql.append(alias).append(".id");

                subQuery.append(" (" + sqlList.get(i).toString() + ") ").append(alias);

                if (i < sqlList.size() -1) {
                    subQuery.append(",");
                    finalSql.append(",");
                }
            }
            finalSql.append(") id");

            finalSql.append(" FROM ").append(subQuery.toString());

        } else if (sqlList.size() == 1) {
            // 当只有一条sql时，无需合并，直接输出
            finalSql.append(sqlList.get(0).toString());
        } else {
            return null;
        }

        return finalSql.toString();
    }

    /**
     * 组件条件
     * @param sql
     * @param params
     * @param rule
     */
    private static void appendCondition(StringBuilder sql, List<Object> params, JSONObject rule) {
        String column = rule.getStr("column");
        String func = rule.getStr("func");
        String operator = rule.getStr("operator");
        Object value = rule.get("value");
        boolean isrelation = rule.getBool("is_relation");

        if (isrelation) { // 当满足关联查询时，做特殊处理
            JSONObject relationConfig = rule.getJSONObject("relation_config");

            String relationTable = relationConfig.getStr("relation_table");
            Entity relationWhereColumn = RedisUtil.get("db_column" + relationConfig.getStr("relation_where_column"));
            Entity relationSelectColumn = RedisUtil.get("db_column" + relationConfig.getStr("relation_select_column"));

            sql.append("bitmap_contains((SELECT bitmap_union(bitmap_hash("+relationSelectColumn.getStr("code")+")) FROM " +relationTable+ " WHERE COALESCE(" +relationWhereColumn.getStr("code")+ ", 0) ");
            inValues(sql, params, operator, value);
            sql.append("),").append("bitmap_to_string(bitmap_hash(" + column+ ")))");
            return;
        }

        if (StrUtil.isNotBlank(func)) {
            switch (func) {
                case "agos":
                    column = "DATEDIFF(NOW(), " + column + ")";
                    break;
                case "yyyy":
                    column = "DATE_FORMAT(" + column + ", '%Y') ";
                    break;
                case "yyyymm":
                    column = "DATE_FORMAT(" + column + ", '%Y-%m') ";
                    break;
                case "yyyymmdd":
                    column = "DATE_FORMAT(" + column + ", '%Y-%m-%d') ";
                    break;
                case "yyyymmddhhmm":
                    column = "DATE_FORMAT(" + column + ", '%Y-%m-%d %H:%i') ";
                    break;
            }
        }

        //如果为int类型，则需要将为null的值更改为0
        if (rule.getStr("column_type").equals("int")) {
            column = "COALESCE("+column+", 0)";
        }

        switch (operator) {
            case "in":
                sql.append(column);
                inValues(sql, params, operator, value);
                break;

            case "not in":
                sql.append(column);
                inValues(sql, params, operator, value);
                break;

            case "between":
                JSONArray betweenValues = (JSONArray) value;
                sql.append(column).append(" BETWEEN ? AND ?");
                params.add(betweenValues.get(0));
                params.add(betweenValues.get(1));
                break;

            default:
                sql.append(column).append(" ").append(operator).append(" ?");
                params.add(value);

        }
    }

    private static void inValues(StringBuilder sql, List<Object> params, String operator, Object value) {
        JSONArray inValues = (JSONArray) value;

        sql.append(" ").append(operator).append(" (")
                .append(String.join(",", Collections.nCopies(inValues.size(), "?")))
                .append(")");
        for (int i = 0; i < inValues.size(); i++) {
            params.add(inValues.get(i));
        }
    }
}
